﻿using System;
using System.Collections;
using System.Text;
using FTools.Dominio;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using FTools.Generico;

namespace FTools.Persistencia
{
    class PerPersona
    {
        static string msg = "";

        //OBTENER PERSONA
        public Persona obtenerPersona(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            Persona objPersona = new Persona();
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];
                    PerCiudad objPerCiudad = new PerCiudad();
                    Ciudad objCiudad = objPerCiudad.obtenerCiudad(xConexion, xTransaccion, (long)fila.ItemArray[10]);

                    objPersona.IdPersona = (long)fila.ItemArray[0];
                    objPersona.Nombre = (String)fila.ItemArray[1]; objPersona.Nombre = objPersona.Nombre.Trim();
                    objPersona.Direccion = (String)fila.ItemArray[2]; objPersona.Direccion = objPersona.Direccion.Trim();
                    objPersona.Puerta = (long)fila.ItemArray[3];
                    objPersona.Complemento = (String)fila.ItemArray[4]; objPersona.Complemento = objPersona.Complemento.Trim();
                    objPersona.Cp = (long)fila.ItemArray[5];
                    objPersona.Telefono = (String)fila.ItemArray[6]; objPersona.Telefono = objPersona.Telefono.Trim();
                    objPersona.Celular = (String)fila.ItemArray[7]; objPersona.Celular = objPersona.Celular.Trim();
                    objPersona.Email = (String)fila.ItemArray[8]; objPersona.Email = objPersona.Email.Trim();
                    objPersona.Tipo = (String)fila.ItemArray[9]; objPersona.Tipo = objPersona.Tipo.Trim();
                    objPersona.ObjCiudad = objCiudad;
                }
                return objPersona;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //EXISTE PERSONA
        public Boolean existePersona(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select count(*) as EXISTE from PERSONA where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];
                    if ((int)fila.ItemArray[0] > 0)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //OBTENER COLECCION PERSONA
        public ArrayList obtenerColeccionPersona(SqlConnection xConexion, SqlTransaction xTransaccion)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersona = new ArrayList();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA");

                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Persona objPersona = new Persona();
                    DataRow fila = dataTable.Rows[i];
                    PerCiudad objPerCiudad = new PerCiudad();
                    Ciudad objCiudad = objPerCiudad.obtenerCiudad(xConexion, xTransaccion, (long)fila.ItemArray[10]);

                    objPersona.IdPersona = (long)fila.ItemArray[0];
                    objPersona.Nombre = (String)fila.ItemArray[1]; objPersona.Nombre = objPersona.Nombre.Trim();
                    objPersona.Direccion = (String)fila.ItemArray[2]; objPersona.Direccion = objPersona.Direccion.Trim();
                    objPersona.Puerta = (long)fila.ItemArray[3];
                    objPersona.Complemento = (String)fila.ItemArray[4]; objPersona.Complemento = objPersona.Complemento.Trim();
                    objPersona.Cp = (long)fila.ItemArray[5];
                    objPersona.Telefono = (String)fila.ItemArray[6]; objPersona.Telefono = objPersona.Telefono.Trim();
                    objPersona.Celular = (String)fila.ItemArray[7]; objPersona.Celular = objPersona.Celular.Trim();
                    objPersona.Email = (String)fila.ItemArray[8]; objPersona.Email = objPersona.Email.Trim();
                    objPersona.Tipo = (String)fila.ItemArray[9]; objPersona.Tipo = objPersona.Tipo.Trim();
                    objPersona.ObjCiudad = objCiudad;
                    colPersona.Add(objPersona);
                }
                return colPersona;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //OBTENER BUSQUEDA PERSONA
        public ArrayList obtenerBusquedaPersona(SqlConnection xConexion, SqlTransaction xTransaccion, String xNombre)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersona = new ArrayList();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA where " +
                                                    "(NOMBRE like '%' + @Nombre + '%' or @Nombre='')");

                command.Parameters.AddWithValue("@Nombre", xNombre);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    Persona objPersona = new Persona();
                    DataRow fila = dataTable.Rows[i];
                    PerCiudad objPerCiudad = new PerCiudad();
                    Ciudad objCiudad = objPerCiudad.obtenerCiudad(xConexion, xTransaccion, (long)fila.ItemArray[10]);

                    objPersona.IdPersona = (long)fila.ItemArray[0];
                    objPersona.Nombre = (String)fila.ItemArray[1]; objPersona.Nombre = objPersona.Nombre.Trim();
                    objPersona.Direccion = (String)fila.ItemArray[2]; objPersona.Direccion = objPersona.Direccion.Trim();
                    objPersona.Puerta = (long)fila.ItemArray[3];
                    objPersona.Complemento = (String)fila.ItemArray[4]; objPersona.Complemento = objPersona.Complemento.Trim();
                    objPersona.Cp = (long)fila.ItemArray[5];
                    objPersona.Telefono = (String)fila.ItemArray[6]; objPersona.Telefono = objPersona.Telefono.Trim();
                    objPersona.Celular = (String)fila.ItemArray[7]; objPersona.Celular = objPersona.Celular.Trim();
                    objPersona.Email = (String)fila.ItemArray[8]; objPersona.Email = objPersona.Email.Trim();
                    objPersona.Tipo = (String)fila.ItemArray[9]; objPersona.Tipo = objPersona.Tipo.Trim();
                    objPersona.ObjCiudad = objCiudad;
                    colPersona.Add(objPersona);
                }
                return colPersona;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //ALTA PERSONA
        public int altaPersona(SqlConnection xConexion, SqlTransaction xTransaccion, Persona xObjPersona)
        {
            try
            {
                SqlCommand command = new SqlCommand("insert into PERSONA values (@IdPersona, @Nombre, @Direccion, @Puerta, @Complemento, @Cp, @Telefono, @Celular, @Email, @Tipo, @IdCiudad)");
                command.Parameters.AddWithValue("@IdPersona", xObjPersona.IdPersona);
                command.Parameters.AddWithValue("@Nombre", xObjPersona.Nombre.Trim());
                command.Parameters.AddWithValue("@Direccion", xObjPersona.Direccion.Trim());
                command.Parameters.AddWithValue("@Puerta", xObjPersona.Puerta);
                command.Parameters.AddWithValue("@Complemento", xObjPersona.Complemento.Trim());
                command.Parameters.AddWithValue("@Cp", xObjPersona.Cp);
                command.Parameters.AddWithValue("@Telefono", xObjPersona.Telefono.Trim());
                command.Parameters.AddWithValue("@Celular", xObjPersona.Celular.Trim());
                command.Parameters.AddWithValue("@Email", xObjPersona.Email.Trim());
                command.Parameters.AddWithValue("@Tipo", xObjPersona.Tipo.Trim());
                command.Parameters.AddWithValue("@IdCiudad", xObjPersona.ObjCiudad.IdCiudad);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //BAJA PERSONA
        public int bajaPersona(SqlConnection xConexion, SqlTransaction xTransaccion, Persona xObjPersona)
        {
            try
            {
                SqlCommand command = new SqlCommand("delete from PERSONA where ID_PERSONA=@IdPersona");
                command.Parameters.AddWithValue("@IdPersona", xObjPersona.IdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //MODIFICA PERSONA
        public int modificaPersona(SqlConnection xConexion, SqlTransaction xTransaccion, Persona xObjPersona)
        {
            try
            {
                SqlCommand command = new SqlCommand("update PERSONA set NOMBRE=@Nombre, DIRECCION=@Direccion, PUERTA=@Puerta, COMPLEMENTO=@Complemento, CP=@Cp, TELEFONO=@Telefono, CELULAR=@Celular, EMAIL=@Email, TIPO=@Tipo, ID_CIUDAD=@IdCiudad " + 
                                                    "where ID_PERSONA = @IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xObjPersona.IdPersona);
                command.Parameters.AddWithValue("@Nombre", xObjPersona.Nombre.Trim());
                command.Parameters.AddWithValue("@Direccion", xObjPersona.Direccion.Trim());
                command.Parameters.AddWithValue("@Puerta", xObjPersona.Puerta);
                command.Parameters.AddWithValue("@Complemento", xObjPersona.Complemento.Trim());
                command.Parameters.AddWithValue("@Cp", xObjPersona.Cp);
                command.Parameters.AddWithValue("@Telefono", xObjPersona.Telefono.Trim());
                command.Parameters.AddWithValue("@Celular", xObjPersona.Celular.Trim());
                command.Parameters.AddWithValue("@Email", xObjPersona.Email.Trim());
                command.Parameters.AddWithValue("@Tipo", xObjPersona.Tipo.Trim());
                command.Parameters.AddWithValue("@IdCiudad", xObjPersona.ObjCiudad.IdCiudad);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }
    }
}
